﻿
/*
 * Create login: NT AUTHORITY\NETWORK SERVICE
 * Typically, this is the account that IIS uses to execute web applications.
 */
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'U' AND [name] = 'NT AUTHORITY\NETWORK SERVICE')
BEGIN
    CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE];
END
GO


/*
 * The following code is used for grant EXECUTE access to all "sproc_.." stored procedures
 * for "NT AUTHORITY\NETWORK SERVICE" account.
 */
DECLARE @sql nvarchar(256);
DECLARE @name nvarchar(128);
DECLARE stored_procedure_cursor CURSOR FOR SELECT [name] FROM sys.procedures WHERE [name] LIKE 'sproc_%';

OPEN stored_procedure_cursor;
FETCH NEXT FROM stored_procedure_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'GRANT EXECUTE ON dbo.' + @name + ' TO [NT AUTHORITY\NETWORK SERVICE]';
    exec(@sql);

    FETCH NEXT FROM stored_procedure_cursor INTO @name;
END

CLOSE stored_procedure_cursor;
DEALLOCATE stored_procedure_cursor;
GO